package net.sf.rose.jdbc;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.List;
import java.util.Map;

import net.sf.rose.exception.SystemErrorCode;
import net.sf.rose.exception.SystemException;
import net.sf.rose.jdbc.annotation.cache.ORMConfigBean;
import net.sf.rose.jdbc.annotation.cache.ORMConfigBeanCache;
import net.sf.rose.jdbc.service.Service;


import org.apache.log4j.Logger;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;

/**
 * 功能：自动拼接SQL语句
 * <p><br>此类封装的数据库操作都是基于实体Bean的对象映射操作的。
 * @author fengjian
 * @since 2013/05/18
 * @see QuerySQL
 * @see QueryMapSQL
 */
public class QueryBeanSQL extends QuerySQL {

	private static Logger logger = Logger.getLogger(QueryBeanSQL.class);
	
	/** 数据库服务类 */
	protected Service service = null;

	/**
	 * 默认构造函数
	 */
	public QueryBeanSQL() {
		super();
	}

	/**
	 * 返回数据库操作的执行者
	 * @param service - 数据库连接服务提供类
	 */
	public QueryBeanSQL(Service service) {
		super();
		this.service = service;
	}

	/**
	 * 返回数据库操作的执行者
	 */
	public UserBean getUser(){
		return this.service.getUser();
	}

	/**
	 * 功能:拼装SQL查询语句
	 * @param entityClass - 返回对象类型
	 * @param pk          - 主键值
	 */
	public void createLoadSql(Class<?> entityClass, Object pk) {
		this.entityClass = entityClass;
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		StringBuilder buf = new StringBuilder();
		buf.append("SELECT * FROM ");
		buf.append(config.getTableName());
		buf.append(" WHERE ");
		buf.append(config.getIdName()).append("=?");
		this.sql = buf.toString();
		list.clear();
		list.add(pk);
	}

	/**
	 * 功能:拼装SQL查询语句
	 * @param entityClass - 返回对象类型
	 * @param sql         - SQL语句
	 * @param pk          - 主键值
	 */
	public void createLoadSql(Class<?> entityClass, String sql, String pk) {
		list.clear();
		list.add(pk);
		this.sql = sql;
		this.entityClass = entityClass;
	}

	/**
	 * 功能:拼装SQL查询语句
	 * @param entityClass  - 返回对象类型
	 * @param sql          - SQL语句
	 * @param map          - 参数键值列表
	 */
	public void createListSql(Class<?> entityClass, String sql, Map<String, Object> map) {
		String key = null;
		this.entityClass = entityClass;
		StringBuilder sb = new StringBuilder(sql);
		list.clear();
		int i = sb.indexOf("#");
		if (i == -1) {
			this.sql = sql;
			return;
		}
		int j = sb.indexOf("#", i + 1);
		do {
			key = sb.substring(i + 1, j);
			list.add(map.get(key));
			sb.replace(i, j + 1, "?");
			i = sb.indexOf("#");
			j = sb.indexOf("#", i + 1);
		} while (i != -1 && j != -1);
		this.sql = sb.toString();
	}

	/**
	 * 功能:拼装SQL查询列表语句
	 * @param bean     - 参数对象
	 */
	public void createListSql(Object bean) {
		String key = null;
		Object val = null;
		list.clear();
		this.entityClass = bean.getClass();
		StringBuilder sb = new StringBuilder("SELECT * FROM ");
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		sb.append(config.getTableName());
		sb.append(" WHERE ");
		BeanWrapper bw = new BeanWrapperImpl(bean);
		for(Field f :entityClass.getDeclaredFields()){
			key = f.getName();
			val = bw.getPropertyValue(key);
			if (val != null) {
				sb.append(config.getDBNameByFieldName(key)).append("=? ").append(" AND ");
				list.add(val);
			}
		}
		// end with 'and '
		sb = new StringBuilder(sb.toString().trim());
		if (sb.length() - sb.lastIndexOf("AND") == 3) {
			sb.delete(sb.length() - 3, sb.length());
		}
		// end with 'where '
		if (sb.length() - sb.lastIndexOf("WHERE") == 5) {
			sb.delete(sb.length() - 5, sb.length());
		}
		this.sql = sb.toString();
	}

	/**
	 * 功能:拼装SQL查询列表语句
	 * @param entityClass  - 返回对象类型
	 * @param sql          - SQL语句
	 * @param bean         - 参数对象
	 */
	public void createListSql(Class<?> entityClass, String sql, Object bean) {
		String key = null;
		this.entityClass = entityClass;
		StringBuilder sb = new StringBuilder(sql);
		list.clear();
		int i = sb.indexOf("#");
		if (i == -1) {
			this.sql = sql;
			return;
		}
		BeanWrapper bw = new BeanWrapperImpl(bean);
		int j = sb.indexOf("#", i + 1);
		do {
			key = sb.substring(i + 1, j);
			list.add(bw.getPropertyValue(key));
			sb.replace(i, j + 1, "?");
			i = sb.indexOf("#");
			j = sb.indexOf("#", i + 1);
		} while (i != -1 && j != -1);
		this.sql = sb.toString();
	}
	
	/**
	 * 功能:拼装SQL插入语句
	 * @param bean - 参数对象
	 * @throws SystemException - 如果操作失败将抛出异常
	 */
	public void createInsertSql(Object bean) {
		Object val = null;
		this.entityClass = bean.getClass();
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		String tablename = config.getTableName();
		StringBuffer buf = new StringBuffer();
		StringBuffer valbuf = new StringBuffer();
		list.clear();

		BeanWrapper bw = new BeanWrapperImpl(bean);
		buf.append("INSERT INTO ");
		buf.append(tablename);
		buf.append("(");

		valbuf.append("VALUES(");
		try {
			String key = null;
			for(Field f :entityClass.getDeclaredFields()){
				// 如果属性使用transient修饰符表示不需要持久化保存
				if (Modifier.isTransient(f.getModifiers())) {
					continue;
				}
				key = f.getName();
				val = bw.getPropertyValue(key);
				if (val != null) {
					buf.append(config.getDBNameByFieldName(key));
					buf.append(",");

					valbuf.append("?,");
					if(val instanceof java.util.Date){
						val = new java.sql.Timestamp(((java.util.Date)val).getTime());
					}
					list.add(val);
				}
			}
		} catch (Exception e) {
			String message = "数据库插入SQL语句创建失败！";
			if (buf != null) {
				logger.error("sql:" + buf.toString());
			}
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.DAO_SQL_INSERT_CREATE_ERROR);
		}
		buf.replace(buf.length() - 1, buf.length(), ")");
		valbuf.replace(valbuf.length() - 1, valbuf.length(), ")");
		buf.append(valbuf);
		sql = buf.toString();
	}

	/**
	 * 功能:拼装SQL更新语句
	 * @param bean - 参数对象
	 * @throws SystemException - 如果操作失败将抛出异常
	 */
	public void createUpdateSql(Object bean) {
		this.entityClass = bean.getClass();
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		String idname = config.getIdName();
		StringBuffer buf = new StringBuffer();
		buf.append("UPDATE ");
		buf.append(config.getTableName());
		buf.append(" SET ");
		list.clear();

		Object val = null;
		Object id = null;
		BeanWrapper bw = new BeanWrapperImpl(bean);
		try {
			String key = null;
			String dbkey = null;
			for(Field f :getEntityClass().getDeclaredFields()){
				// 如果属性使用transient修饰符表示不需要持久化保存
				if (Modifier.isTransient(f.getModifiers())) {
					continue;
				}
				key = f.getName();
				dbkey = config.getDBNameByFieldName(key);
				if (idname.equals(dbkey)) {
					id = bw.getPropertyValue(key);
					continue;
				}
				val = bw.getPropertyValue(key);
				if (val != null) {
					// 如果是数字型，又是默认值的则不更新
					if (val instanceof Number) {
						String s = val.toString();
						if (s.equals("0") || s.equals("0.0")) {
							continue;
						}
					}
					buf.append(config.getDBNameByFieldName(key)).append("=?, ");
					list.add(val);
				}
			}
		} catch (Exception e) {
			String message = "数据库更新SQL语句创建失败！";
			if (buf != null) {
				logger.error("sql:" + buf.toString());
			}
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.DAO_SQL_UPDATE_CREATE_ERROR);
		}
		// 如果没有需要更新的字段，则返回空参数列表
		if (list.size() == 0) {
			return;
		}
		// 拼接SQL条件语句
		buf.deleteCharAt(buf.length() - 2);
		buf.append(" WHERE ");
		buf.append(idname);
		buf.append("=? ");
		sql = buf.toString();
		list.add(id);
	}

	/**
	 * 功能:拼装SQL更新全部字段语句
	 * <p><br>特别地，此方法将对比两个类的所有属性(包括空值)，有任何不同都将更新至数据库。
	 * @param old   - 参数数据库对象类
	 * @param bean  - 参数更新对象类
	 * @throws SystemException - 如果操作失败将抛出异常
	 */
	public void createUpdateAllSql(Object old, Object bean) {
		this.entityClass = bean.getClass();
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		String idname = config.getIdName();
		StringBuilder buf = new StringBuilder();
		buf.append("UPDATE ");
		buf.append(config.getTableName());
		buf.append(" SET ");
		list.clear();

		Object id = null;
		Object oldval = null;
		Object newval = null;
		BeanWrapper bwold = new BeanWrapperImpl(old);
		BeanWrapper bwnew = new BeanWrapperImpl(bean);
		try {
			for(Field f :getEntityClass().getDeclaredFields()){
				// 如果属性使用transient修饰符表示不需要持久化保存
				if (Modifier.isTransient(f.getModifiers())) {
					continue;
				}
				String name = f.getName();
				if (idname.equals(name)) {
					id = bwold.getPropertyValue(name);
					continue;
				}
				oldval = bwold.getPropertyValue(name);
				newval = bwnew.getPropertyValue(name);
				if (oldval == null) {
					if (newval == null) {
						continue;
					} else {
						buf.append(config.getDBNameByFieldName(name)).append("=?, ");
						list.add(newval);
					}
				} else {
					if (newval == null) {
						buf.append(config.getDBNameByFieldName(name)).append("=?, ");
						list.add(null);
					} else if (!oldval.toString().equals(newval.toString())) {
						buf.append(config.getDBNameByFieldName(name)).append("=?, ");
						list.add(newval);
					}
				}
			}
		} catch (Exception e) {
			String message = "数据库批量SQL语句创建失败！";
			if (buf != null) {
				logger.error("sql:" + buf.toString());
			}
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.DAO_SQL_BATCH_UPDATE_CREATE_ERROR);
		}
		// 如果没有需要更新的字段，则返回空参数列表
		if (list.size() == 0) {
			return;
		}
		// 拼接SQL条件语句
		buf.deleteCharAt(buf.length() - 2);
		buf.append(" WHERE ");
		buf.append(idname);
		buf.append("=? ");
		sql = buf.toString();
		list.add(id);
	}

	/**
	 * 功能:拼装SQL删除语句
	 * @param entityClass   - 返回对象类型
	 * @param id            - 主键值
	 */
	public void createDeleteSql(Class<?> entityClass, Object id) {
		this.entityClass = entityClass;
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		StringBuffer buf = new StringBuffer();
		buf.append("DELETE FROM ");
		buf.append(config.getTableName());
		buf.append(" WHERE ");
		buf.append(config.getIdName());
		buf.append("=? ");
		sql = buf.toString();
		list.clear();
		list.add(id);
	}

	/**
	 * 功能:拼装SQL删除语句
	 * @param entityClass - 返回对象类型
	 * @param bean        - 主键值
	 */
	public void createDeleteSql(Object bean) {
		this.entityClass = bean.getClass();
		BeanWrapper bw = new BeanWrapperImpl(bean);
		ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
		Object id = bw.getPropertyValue(config.getIdName());
		StringBuffer buf = new StringBuffer();
		buf.append("DELETE FROM ");
		buf.append(config.getTableName());
		config.getIdName();
		buf.append(" WHERE ");
		buf.append(config.getIdName());
		buf.append("=? ");
		sql = buf.toString();
		list.clear();
		list.add(id);
	}

	/**
	 * 功能:拼装SQL查询列表语句
	 * @param sql    - 批处理SQL语句
	 * @param beans  - 类对象列表
	 */
	public void createBatchSql(String sql, List<?> beans) {
		String key = null;
		this.entityClass = beans.get(0).getClass();
		StringBuilder sb = new StringBuilder(sql);
		list.clear();
		int i = sb.indexOf("#");
		if (i == -1) {
			this.sql = sql;
			return;
		}
		for (int k = 0; k < beans.size(); k++) {
			list.add(new java.util.ArrayList<Object>());
		}
		BeanWrapperImpl bw = new BeanWrapperImpl();
		int j = sb.indexOf("#", i + 1);
		do {
			key = sb.substring(i + 1, j);
			for (int k = 0; k < beans.size(); k++) {
				bw.setWrappedInstance(beans.get(k));
				list.add(bw.getPropertyValue(key));
			}
			sb.replace(i, j + 1, "?");
			i = sb.indexOf("#");
			j = sb.indexOf("#", i + 1);
		} while (i != -1 && j != -1);
		this.sql = sb.toString();
	}

}
